Imports System.Windows.Forms

Public Class frmCalcWQI

    Private dgvRecords As WRDB.Controls.DGVEditor

    Private Sub frmCalcWQI_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        For i As Integer = 1 To 13
            Project.FillPickList(Choose(i, cboDoSat, cboFecal, cbopH, cboBod, cboTemp1, cboTemp2, cboP1, cboP2, cboN1, cboN2, cboTurb, cboTSS1, cboTSS2), clsProject.TempData, "PCodes", "PCode", "Anal_Name", "(Not Used)", , String.Format("WHERE Selected_By='{0}'", clsApplicationSettings.UserName))
        Next
        cboTemp1.Items.Add("(Set Temperature Change to Zero)")
        Project.FillPickList(cboWQI, "", "PCodes", "PCode", "Anal_Name", "(Create New)", GetSetting(REGAPP, Name, cboWQI.Name))
        Project.FillPickList(cboCCode, "", "CCodes", "CCode", "CCode_Name", "(Create New)", GetSetting(REGAPP, Name, cboCCode.Name))
        Project.FillPickListValidation(cboLCode, "LCode", , GetSetting(REGAPP, Name, cboLCode.Name))
        frmCalc.RefreshTableList(cboTableName)
        GetControlValues(REGAPP, Me)
        dgvRecords = New Controls.DGVEditor(dgRecords)
    End Sub

    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Try
            With TabControl1
                If .SelectedIndex = 1 Then
                    Dim TableName As String = cboTableName.Text
                    If cboTableName.SelectedIndex = 0 Then TableName = ActiveBrowser.FullTableName
                    Dim DataTable As New clsDataTable(Project, TableName)
                    Try
                        Dim PCode As String = Project.GetPickList(cboWQI)
                        Dim CCode As String = Project.GetPickList(cboCCode)
                        Dim LCode As String = Project.GetPickList(cboLCode)
                        If PCode.StartsWith("(Create") Or CCode.StartsWith("(Create") Then
                            WarningMsg("You must select a valid PCode and CCode before continuing.")
                            Exit Sub
                        End If

                        Dim NumFound As Integer = DataTable.GetCount("PCode='{0}' AND CCode='{1}' AND LCode='{2}'", PCode, CCode, LCode)
                        If NumFound > 0 Then
                            Select Case MessageBox.Show(StringFormat("Before computing all values, do you want to delete all {0} existing records in this Working table that have the specified PCode ({1}), CCode ({2}) and LCode ({3})?", NumFound, PCode.Trim, CCode.Trim, LCode.Trim), "Calculate Water Quality Index", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
                                Case Windows.Forms.DialogResult.Yes
                                    DataTable.DeleteRecords("PCode='{0}' AND CCode='{1}' AND LCode='{2}'", PCode, CCode, LCode)
                                Case Windows.Forms.DialogResult.No
                                Case Windows.Forms.DialogResult.Cancel
                                    Exit Sub
                            End Select
                        End If

                        DataTable.BeginTransaction()

                        'save computed values to table
                        With dgRecords
                            Dim NumCols As Integer = 1
                            Dim NumComputed As Integer = NumCols * .Rows.Count
                            If MessageBox.Show(StringFormat("You are about to append {0} computed values to the selected Working table ({1}). These were calculated based upon the input PCodes you selected. The calculated values will use the PCode {2}, CCode {3} and LCode {4}.\n\nBecause the table will be permanently modified, it may be difficult to undo this operation. Are you sure you want to continue?", NumComputed, TableName, cboWQI.Text, cboCCode.Text, cboLCode.Text), "Calculate Water Quality Index", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                                Try
                                    For i As Integer = 0 To .Rows.Count - 1
                                        If Not PCode.StartsWith("(") Then
                                            Dim DataRecord As New clsDataRecord(.Rows(i).Cells(0).Value, .Rows(i).Cells(1).Value, PCode, .Rows(i).Cells(.ColumnCount - 1).Value, CCode, , , , , , , LCode)
                                            If Not DataTable.InsertRecord(DataRecord) Then WarningMsg("Unable to update table.") : Exit Sub
                                        End If
                                        MainForm.WriteProgress(i, .Rows.Count - 1, "Saving computed values...", ProgressBarStyle.Continuous)
                                    Next
                                    DataTable.Close(clsDataTable.enumEndTransaction.Commit)
                                    clsApplicationSettings.ResetEditTime()
                                Catch ex As Exception
                                    DataTable.Close(clsDataTable.enumEndTransaction.Rollback)
                                Finally
                                    DataTable.Close()
                                End Try
                                Project.UpdateTrackingRecord(TableName, Project.GetRecordCount(TableName), True)
                                Project.UpdateOwnerField(TableName)
                                frmJournal.AddJournalEntry(TableName, frmJournal.enumJournalEntry.Calculate, NumComputed, , "Calculated WQI")
                                RefreshAllForms()
                                SaveControlValues(REGAPP, Me)
                                Close()
                            End If
                            MainForm.OpenDataTable(TableName)
                        End With
                    Catch ex As Exception
                    Finally
                        DataTable.Close()
                    End Try
                Else
                    .SelectedIndex += 1
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub cboTableName_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableName.SelectionChangeCommitted
        frmCalc.TableSelectionCommitted(cboTableName)
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        Try
            Select Case TabControl1.SelectedIndex
                Case 1
                    Dim NotSelected As Boolean = False
                    For i As Integer = 1 To 3
                        With CType(Choose(i, cboWQI, cboCCode, cboLCode), ComboBox)
                            If .SelectedIndex = -1 Then NotSelected = True : Exit For
                        End With
                    Next
                    If NotSelected Then
                        TabControl1.SelectedIndex = 0
                        Application.DoEvents()
                        WarningMsg("You must specify the PCode, CCode and LCode of the calculated WQI values before you can continue.")
                        Exit Sub
                    End If
            End Select

            If TabControl1.SelectedIndex = 1 Then
                btnNext.Text = "&Finish"
                BuildCrosstab()
            Else
                btnNext.Text = "&Next"
                dgRecords.Columns.Clear()
            End If

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub BuildCrosstab()
        Try
            Me.Enabled = False
            Dim dictCrosstab As New Generic.SortedDictionary(Of String, Single())
            Dim Hdgs As String = "Station ID~Date-Time"
            Dim SomeDuplicates As Boolean = False
            For i As Integer = 1 To 13
                Dim PCode As String = Project.GetPickList(Choose(i, cboDoSat, cboFecal, cbopH, cboBod, cboTemp1, cboTemp2, cboP1, cboP2, cboN1, cboN2, cboTurb, cboTSS1, cboTSS2))
                Hdgs &= "~" & PCode
                If PCode = "(Not Used)" Then Continue For
                Dim sql As String = String.Format("SELECT Station_ID,Date_Time,Result FROM {0} Dta WHERE PCode='{1}' AND Selected_BY='{2}'", clsProject.TempData, PCode, clsApplicationSettings.UserName)
                Dim dr As DbDataReader = Project.DB.ExecuteReader(sql)
                While dr IsNot Nothing AndAlso dr.Read
                    Dim key As String = TestNull(dr.GetValue(0), "") & vbTab & CDate(TestNull(dr.GetValue(1), "1/1/1900")).ToString("yyyy-MM-dd")
                    If Not dictCrosstab.ContainsKey(key) Then
                        Dim ar(12) As Single
                        For j As Integer = 0 To 12
                            ar(j) = Single.MinValue
                        Next
                        dictCrosstab.Add(key, ar)
                    End If
                    If dictCrosstab(key)(i - 1) <> Single.MinValue Then SomeDuplicates = True
                    dictCrosstab(key)(i - 1) = TestNull(dr.GetValue(2), Single.MinValue)
                End While
                dr.Close()
                If Not MainForm.WriteProgress(i, 13, "Compiling data...", ProgressBarStyle.Blocks, True) Then Exit Sub
            Next
            With dgvRecords
                .SetHeadings(Hdgs & "~WQI", False, 0)
            End With
            With dgRecords
                .SuspendLayout()
                .DataSource = Nothing
                .ReadOnly = True
                .RowHeadersVisible = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = False
                .AllowUserToResizeRows = False
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                For c As Integer = 0 To .ColumnCount - 1
                    With .Columns(c)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                        Select Case c
                            Case 0 : .DefaultCellStyle.Format = ""
                            Case 1 : .DefaultCellStyle.Format = MyDateTimeFormat()
                            Case 4, 6, 7 : .DefaultCellStyle.Format = "N1"
                            Case 2, 5, 8 To 14 : .DefaultCellStyle.Format = "N2"
                            Case Else : .DefaultCellStyle.Format = "N0"
                        End Select
                        Select Case c
                            Case 0 : .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft : .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                            Case 1 : .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter : .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                            Case dgRecords.ColumnCount - 1
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight : .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                            Case Else
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight : .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                                Dim PCode As String = Project.GetPickList(Choose(c - 1, cboDoSat, cboFecal, cbopH, cboBod, cboTemp1, cboTemp2, cboP1, cboP2, cboN1, cboN2, cboTurb, cboTSS1, cboTSS2))
                                If PCode = ("(Not Used)") Or PCode.StartsWith("(Set ") Then .Visible = False
                        End Select

                    End With
                Next
                Dim SomeMissing As Boolean = False
                Dim Num As Integer = 0
                For Each kv As Generic.KeyValuePair(Of String, Single()) In dictCrosstab
                    Dim ar() As String = kv.Key.Split(vbTab)
                    .Rows.Add(ar(0), CDate(ar(1)).ToString(MyDateFormat))
                    For i As Integer = 0 To 12
                        .Rows(.RowCount - 1).Cells(i + 2).Value = IIf(kv.Value(i) = Single.MinValue, DBNull.Value, kv.Value(i))
                    Next
                    Dim WQIndex As Single = WQI(kv.Value)
                    If WQIndex = Single.MinValue Then
                        SomeMissing = True
                        .Rows(.RowCount - 1).Cells(.ColumnCount - 1).Value = DBNull.Value
                    Else
                        .Rows(.RowCount - 1).Cells(.ColumnCount - 1).Value = Math.Round(WQIndex, 0)
                    End If
                    Num += 1
                    If Not MainForm.WriteProgress(Num, dictCrosstab.Count, "Calculating WQI values...", ProgressBarStyle.Blocks, True) Then Exit Sub
                Next
                .ResumeLayout(True)
                If SomeMissing Then WarningMsg("One or more WQI values could not be computed due to invalid input data.")
                If SomeDuplicates Then WarningMsg("One or more crosstab entries had multiple values associated with a given Station ID, Date-Time, and PCode; this may be due to duplicates in the selected table or multiple values distinguished only by another field, such as CCode. Examine your data carefully before continuing.")
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            dgRecords.ResumeLayout(True)
            Me.Enabled = True
            MainForm.ProgressComplete()
        End Try
    End Sub

    ''' <summary>
    ''' Given values for each parameter, calculate WQI index
    ''' </summary>
    ''' <param name="ar">Row in crosstab table containing parameter values</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function WQI(ByVal ar() As Single) As Single
        'equations are based on web site http://www.water-research.net/watrqualindex/waterqualityindex.htm (source shows javascript functions)

        'order of parameters is: DO Sat, Fecal, pH, BOD, Temp1, Temp2, Phos1, Phos2, Nitrate1, Nitrate2, Turbidity, TSS1, TSS2

        Dim TotalWQI As Single = 0.0
        Dim SumWt As Single = 0.0

        For i As Integer = 1 To 9
            Dim ParamName As String = Choose(i, "DOSat", "Fecal", "pH", "BOD", "Temp", "Phos", "Nitrate", "Turb", "TSS")
            Dim ParamVal As Single = Single.MinValue
            Select Case i
                Case 1 To 4 : ParamVal = TestNull(ar(i - 1), Single.MinValue)
                Case 5
                    If cboTemp1.Text.StartsWith("(Set") Then
                        ParamVal = 0
                    Else
                        ParamVal = TestNull(ar(4), 0) - TestNull(ar(5), 0) : If IsDBNull(ar(4)) And IsDBNull(ar(5)) Then ParamVal = Single.MinValue
                    End If
                Case 6 : ParamVal = TestNull(ar(6), 0) + TestNull(ar(7), 0) : If IsDBNull(ar(6)) And IsDBNull(ar(7)) Then ParamVal = Single.MinValue
                Case 7 : ParamVal = TestNull(ar(8), 0) + TestNull(ar(9), 0) : If IsDBNull(ar(8)) And IsDBNull(ar(9)) Then ParamVal = Single.MinValue
                Case 8 : ParamVal = TestNull(ar(10), Single.MinValue)
                Case 9 : ParamVal = TestNull(ar(11), 0) + TestNull(ar(12), 0) : If IsDBNull(ar(11)) And IsDBNull(ar(12)) Then ParamVal = Single.MinValue
            End Select
            If ParamVal = Single.MinValue Then
                Debug.Print("No value for {0}; WQI skipped", ParamName)
            End If
            Dim arParm() As Single = Choose(i, New Single() {0, 16, 20, 32, 34, 62, 67, 70, 74, 80, 84, 90, 94, 98, 102, 106, 137, 140},
                                               New Single() {0, 2, 3, 4, 5},
                                               New Single() {2, 3, 3.5, 4, 4.1, 4.5, 4.8, 5.1, 6.2, 6.8, 7, 7.1, 7.2, 7.4, 7.6, 7.8, 8, 8.9, 9.7, 10, 10.3, 10.7, 10.8, 11, 11.5, 12},
                                               New Single() {0, 0.5, 1, 1.5, 2.5, 4, 6.5, 8.5, 11, 15, 17.5, 20, 21.5, 25, 27, 30},
                                               New Single() {-10, 0, 5, 9.5, 12, 14.5, 21, 30},
                                               New Single() {0, 0.2, 0.5, 0.7, 1, 1.3, 1.6, 2, 3.2, 4, 5, 6, 7, 8, 10},
                                               New Single() {0, 2, 3, 3.5, 4, 6, 10, 17, 27, 37, 50, 54, 71, 80, 90, 100},
                                               New Single() {0, 3, 8, 13, 15, 20, 30, 40, 50, 60, 70, 80, 90, 100},
                                               New Single() {0, 10, 20, 30, 40, 60, 70, 150, 450, 500})
            Dim arWQI() As Single = Choose(i, New Single() {2, 10, 12, 20, 22, 60, 70, 75, 80, 87, 90, 95, 98, 99, 99, 98, 80, 78},
                                               New Single() {99, 44, 22, 10, 4},
                                               New Single() {2, 4, 6, 9, 10, 15, 20, 30, 60, 83, 88, 90, 92, 93, 92, 90, 84, 52, 26, 20, 15, 11, 10, 8, 5, 3},
                                               New Single() {100, 98, 95, 90, 70, 61, 48, 40, 30, 20, 15, 12, 10, 7, 6, 5},
                                               New Single() {55, 93, 73, 47, 36, 32, 20, 10},
                                               New Single() {100, 92, 60, 50, 40, 34, 30, 27, 20, 17, 13, 10, 8, 7, 7},
                                               New Single() {97, 95, 90, 80, 70, 60, 51, 40, 30, 20, 10, 8, 4.5, 4, 3, 2.5},
                                               New Single() {99, 90, 80, 70, 67, 61, 53, 45, 39, 33, 29, 25, 22, 17},
                                               New Single() {79, 82, 84, 84.5, 86, 87, 86, 79, 40, 31})
            Dim DefaultWQI As Single = Choose(i, 50, 2, 0, 5, Single.MinValue, 2, 1, 5, 20)
            Dim SubWQI As Single = WQI(ParamVal, arParm, arWQI, DefaultWQI, i = 2)
            If SubWQI <> Single.MinValue Then
                Debug.Print("{0}, {1}, {2}, {3}", i, ParamName, ParamVal, SubWQI)
                Dim wt As Single = Choose(i, 0.17, 0.16, 0.11, 0.11, 0.1, 0.1, 0.1, 0.08, 0.07)
                SumWt += wt
                TotalWQI += wt * SubWQI
            End If
        Next
        Debug.Print("WQI={0}", TotalWQI / SumWt)
        Return TotalWQI / SumWt
    End Function

    Private Function WQI(ByVal ParmVal As Single, ByVal ParmArray() As Single, ByVal WQIArray() As Single, ByVal DefaultWQI As Single, Optional ByVal LogInterp As Boolean = False) As Single
        If LogInterp Then
            If ParmVal < 1 Then Return Single.MinValue 'fecal has min of 1
            ParmVal = Math.Log10(ParmVal)
        End If

        If ParmVal < ParmArray(0) Then Return Single.MinValue
        If ParmVal > ParmArray(ParmArray.Length - 1) Then Return DefaultWQI
        Dim idx As Integer = Array.BinarySearch(ParmArray, ParmVal)
        If idx >= 0 Then 'found exact match
            Return WQIArray(idx)
        Else
            idx = Not (idx) 'next larger value
            Return InterpolateY(ParmVal, ParmArray(idx - 1), WQIArray(idx - 1), ParmArray(idx), WQIArray(idx))
        End If
    End Function

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "21")
    End Sub

    Private Sub cboWQI_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboWQI.SelectionChangeCommitted
        frmCalc.PCodeSelectionCommitted(cboWQI)
    End Sub

    Private Sub cboCCode_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCCode.SelectionChangeCommitted, cboLCode.SelectionChangeCommitted
        frmCalc.CCodeSelectionCommitted(cboCCode)
    End Sub

    Private Sub cboTemp1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTemp1.SelectedIndexChanged
        If cboTemp1.Text.StartsWith("(Set") Then
            lblTemp2.Visible = False
            cboTemp2.SelectedIndex = 0
            cboTemp2.Visible = False
        Else
            lblTemp2.Visible = True
            cboTemp2.Visible = True
        End If
    End Sub

    Private Sub btnJournal_Click(sender As System.Object, e As System.EventArgs) Handles btnJournal.Click
        frmJournal.GetJournalDescription()
    End Sub

End Class
